﻿namespace AnyOffice.DAL
{
    using AnyOffice.Model;
    using Microsoft.ApplicationBlocks.Data;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;

    public class BbsBoardDao
    {
        public bool Delete(SqlConnection conn, int boardId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("UPDATE ");
            builder.Append("\t[dbo].[Attachment] ");
            builder.Append("SET ");
            builder.Append("\t[DeleteFlag] = 1 ");
            builder.Append("WHERE ");
            builder.Append("\t[AttachmentId] IN(SELECT DISTINCT [AttachmentId] FROM [dbo].[Bbs_Comment] WHERE ISNULL([AttachmentId],'')<>'' AND [BoardId] = @BoardId) ");
            builder.Append("DELETE FROM ");
            builder.Append("\t[dbo].[Bbs_Comment] ");
            builder.Append("WHERE ");
            builder.Append("\t[BoardId] = @BoardId ");
            builder.Append("DELETE FROM ");
            builder.Append("\t[dbo].[Bbs_Board] ");
            builder.Append("WHERE ");
            builder.Append("\t[BoardId] = @BoardId ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@BoardId", SqlDbType.Int) };
            commandParameters[0].Value = boardId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public int Insert(SqlConnection conn, BbsBoardData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("INSERT INTO [dbo].[Bbs_Board] ");
            builder.Append("( ");
            builder.Append("\t[BoardNo], ");
            builder.Append("\t[BoardName], ");
            builder.Append("\t[WelcomeText], ");
            builder.Append("\t[DeptId], ");
            builder.Append("\t[Owner], ");
            builder.Append("\t[IsAnonymous] ");
            builder.Append(") ");
            builder.Append("VALUES ");
            builder.Append("( ");
            builder.Append("\t@BoardNo, ");
            builder.Append("\t@BoardName, ");
            builder.Append("\t@WelcomeText, ");
            builder.Append("\t@DeptId, ");
            builder.Append("\t@Owner, ");
            builder.Append("\t@IsAnonymous ");
            builder.Append(") ");
            builder.Append("SELECT SCOPE_IDENTITY() AS RetValue ");
            SqlParameter[] commandParameters = new SqlParameter[6];
            commandParameters[0] = new SqlParameter("@BoardNo", SqlDbType.Int);
            commandParameters[0].Value = data.BoardNo;
            commandParameters[1] = new SqlParameter("@BoardName", SqlDbType.NVarChar, 100);
            commandParameters[1].Value = data.BoardName;
            commandParameters[2] = new SqlParameter("@WelcomeText", SqlDbType.NVarChar, 200);
            if (data.WelcomeTextIsNull)
            {
                commandParameters[2].Value = DBNull.Value;
            }
            else
            {
                commandParameters[2].Value = data.WelcomeText;
            }
            commandParameters[3] = new SqlParameter("@DeptId", SqlDbType.NText);
            if (data.DeptIdIsNull)
            {
                commandParameters[3].Value = DBNull.Value;
            }
            else
            {
                commandParameters[3].Value = data.DeptId;
            }
            commandParameters[4] = new SqlParameter("@Owner", SqlDbType.NText);
            if (data.OwnerIsNull)
            {
                commandParameters[4].Value = DBNull.Value;
            }
            else
            {
                commandParameters[4].Value = data.Owner;
            }
            commandParameters[5] = new SqlParameter("@IsAnonymous", SqlDbType.Bit);
            commandParameters[5].Value = data.IsAnonymous;
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, builder.ToString(), commandParameters);
            if (obj2 != null)
            {
                return Convert.ToInt32(obj2);
            }
            return -1;
        }

        public DataTable SelectAll(SqlConnection conn)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SELECT ");
            builder.Append("\t[BoardId], ");
            builder.Append("\t[BoardNo], ");
            builder.Append("\t[BoardName], ");
            builder.Append("\t[WelcomeText], ");
            builder.Append("\t[DeptId], ");
            builder.Append("\tdbo.GetDeptNames(CAST([DeptId] AS nvarchar(2000))) AS DeptName, ");
            builder.Append("\t[Owner], ");
            builder.Append("\tdbo.GetUserNames(CAST([Owner] AS nvarchar(4000))) AS OwnerName, ");
            builder.Append("\t[IsAnonymous] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Bbs_Board] ");
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString());
            set.Tables[0].TableName = "Bbs_Board";
            return set.Tables[0];
        }

        public DataTable SelectByBoardId(SqlConnection conn, int boardId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SELECT ");
            builder.Append("\t[BoardId], ");
            builder.Append("\t[BoardNo], ");
            builder.Append("\t[BoardName], ");
            builder.Append("\t[WelcomeText], ");
            builder.Append("\t[DeptId], ");
            builder.Append("\tdbo.GetDeptNames(CAST([DeptId] AS nvarchar(2000))) AS DeptName, ");
            builder.Append("\t[Owner], ");
            builder.Append("\tdbo.GetUserNames(CAST([Owner] AS nvarchar(4000))) AS OwnerName, ");
            builder.Append("\t[IsAnonymous] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Bbs_Board] ");
            builder.Append("WHERE ");
            builder.Append("\t[BoardId] = @BoardId ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@BoardId", SqlDbType.Int) };
            commandParameters[0].Value = boardId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Bbs_Board";
            return set.Tables[0];
        }

        public bool Update(SqlConnection conn, BbsBoardData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("UPDATE [dbo].[Bbs_Board] SET ");
            builder.Append("\t[BoardNo] = @BoardNo, ");
            builder.Append("\t[BoardName] = @BoardName, ");
            builder.Append("\t[WelcomeText] = @WelcomeText, ");
            builder.Append("\t[DeptId] = @DeptId, ");
            builder.Append("\t[Owner] = @Owner, ");
            builder.Append("\t[IsAnonymous] = @IsAnonymous ");
            builder.Append("WHERE ");
            builder.Append("\t[BoardId] = @BoardId ");
            SqlParameter[] commandParameters = new SqlParameter[7];
            commandParameters[0] = new SqlParameter("@BoardId", SqlDbType.Int);
            commandParameters[0].Value = data.BoardId;
            commandParameters[1] = new SqlParameter("@BoardNo", SqlDbType.Int);
            commandParameters[1].Value = data.BoardNo;
            commandParameters[2] = new SqlParameter("@BoardName", SqlDbType.NVarChar, 100);
            commandParameters[2].Value = data.BoardName;
            commandParameters[3] = new SqlParameter("@WelcomeText", SqlDbType.NVarChar, 200);
            if (data.WelcomeTextIsNull)
            {
                commandParameters[3].Value = DBNull.Value;
            }
            else
            {
                commandParameters[3].Value = data.WelcomeText;
            }
            commandParameters[4] = new SqlParameter("@DeptId", SqlDbType.NText);
            if (data.DeptIdIsNull)
            {
                commandParameters[4].Value = DBNull.Value;
            }
            else
            {
                commandParameters[4].Value = data.DeptId;
            }
            commandParameters[5] = new SqlParameter("@Owner", SqlDbType.NText);
            if (data.OwnerIsNull)
            {
                commandParameters[5].Value = DBNull.Value;
            }
            else
            {
                commandParameters[5].Value = data.Owner;
            }
            commandParameters[6] = new SqlParameter("@IsAnonymous", SqlDbType.Bit);
            commandParameters[6].Value = data.IsAnonymous;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public DataTable UserTop(SqlConnection conn)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SELECT TOP 10 ");
            builder.Append("\t[Users].[NickName], ");
            builder.Append("\t[Users].[UserName], ");
            builder.Append("\tCOUNT([Bbs_Comment].[CommentId]) AS PostNums  ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Users], ");
            builder.Append("\t[dbo].[Bbs_Comment] ");
            builder.Append("WHERE ");
            builder.Append("\t[Users].[UserId] = [Bbs_Comment].[UserId]  ");
            builder.Append("GROUP BY ");
            builder.Append("\t[Users].[UserName], ");
            builder.Append("\t[Users].[NickName] ");
            builder.Append("ORDER BY ");
            builder.Append("\t[PostNums] DESC ");
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString());
            set.Tables[0].TableName = "Users";
            return set.Tables[0];
        }
    }
}

